﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal
{
    public class RoleDal
    {
        private static readonly Lazy<RoleDal> dal = new Lazy<RoleDal>(() => new RoleDal());
        public static RoleDal Instance { get; } = dal.Value;

        private RoleDal()
        { }

        private const string tableName = "Role";
        private const string tableValueName = "RoleValue";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableName);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询Sql
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        private StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select ID,RoleName,RoleCode,IsAdmain,RoleState,");
            strSql.Append("Notes,SortCode,CreateTime,CreateUser,UpdateTime,UpdateUser ");
            strSql.Append("From " + tableName);
            strSql.Append(" where IsDelete=0 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }

            return strSql;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<Role> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<Role>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="roleId">角色id</param>
        /// <returns></returns>
        public Role GetModelById(int roleId)
        {
            StringBuilder sb = GetSelectSql("and ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",roleId)
            };
            Role model = new Role();
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                // 父表信息
                model = CommonOperation.DataTableToModel<Role>(ds.Tables[0]);

                // 查询子表信息
                StringBuilder strSql2 = new StringBuilder();
                strSql2.Append("select t1.RoleId,t1.MenuId,");
                strSql2.Append("t1.Module,t1.Authority,t2.MenuName ");
                strSql2.Append(" from " + tableValueName);
                strSql2.Append(" t1 left join Menu t2 on t1.MenuId = t2.ID ");
                strSql2.Append(" where t1.RoleId=@roleId order by t1.MenuId ");
                SqlParameter[] parameters2 = { new SqlParameter("@roleId", roleId) };
                DataSet ds2 = SqlHelper.Query(strSql2.ToString(), parameters2);
                if (ds2.Tables[0].Rows.Count > 0)
                {
                    List<RoleValue> models = CommonOperation.ConvertDataTableToModelList<RoleValue>(ds2.Tables[0]);
                    model.RoleValues = models;
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 增加一条用户角色数据
        /// </summary>
        public bool AddRole(Role model)
        {
            // 定义SQL语句和参数
            StringBuilder roleSql = new StringBuilder();
            roleSql.Append("insert into " + tableName + " (");
            roleSql.Append("RoleName,RoleCode,IsAdmain,Notes,SortCode,CreateTime,CreateUser )");
            roleSql.Append(" OUTPUT inserted.ID values (");
            roleSql.AppendFormat("@roleName,@roleCode,@isAdmain,@notes,@sortCode,@createTime,@createUser)");

            string connectionString = SqlHelper.connectionString;
            Action<SqlConnection, SqlTransaction> sqlAction = (connection, transaction) =>
            {
                using (SqlCommand command1 = new SqlCommand(roleSql.ToString(), connection, transaction))
                {
                    command1.CommandType = CommandType.Text;
                    command1.Parameters.AddWithValue("@roleName", model.RoleName);
                    command1.Parameters.AddWithValue("@roleCode", model.RoleCode);
                    command1.Parameters.AddWithValue("@isAdmain", model.IsAdmain);
                    command1.Parameters.AddWithValue("@notes", model.Notes);
                    command1.Parameters.AddWithValue("@sortCode", model.SortCode);
                    command1.Parameters.AddWithValue("@createTime", model.CreateTime);
                    command1.Parameters.AddWithValue("@createUser", model.CreateUser);
                    command1.Connection = connection; // 确保连接已设置

                    // 执行命令并获取自增长ID
                    object result = command1.ExecuteScalar();
                    int newID = 0;
                    if (result != null && result is int) // 检查是否为null并且能否转换为decimal
                    {
                        newID = Convert.ToInt32(result);
                        model.ID = newID;
                    }

                    // 子表数据
                    List<RoleValue> itemList = model.RoleValues;
                    if (itemList != null && itemList.Count > 0)
                    {
                        SqlCommand command2;

                        StringBuilder roleValueSql;
                        foreach (RoleValue itemModel in itemList)
                        {
                            roleValueSql = new StringBuilder();
                            roleValueSql.Append("insert into " + tableValueName + "(");
                            roleValueSql.Append("RoleId,MenuId,Module,Authority)");
                            roleValueSql.Append(" values (");
                            roleValueSql.Append("@roleId,@menuId,@module,@authority)");

                            using (command2 = new SqlCommand(roleValueSql.ToString(), connection, transaction))
                            {
                                command2.CommandType = CommandType.Text;
                                command2.Parameters.AddWithValue("@roleId", newID);
                                command2.Parameters.AddWithValue("@menuId", itemModel.MenuId);
                                command2.Parameters.AddWithValue("@module", itemModel.Module);
                                command2.Parameters.AddWithValue("@authority", itemModel.Authority);

                                command2.ExecuteNonQuery();
                            }
                        }
                    }
                }
            };

            // 调用DatabaseHelper中的静态方法ExecuteTransaction
            SqlHelper.ExecuteTransaction(sqlAction, connectionString);
            return true;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateRole(Role model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableName + " set ");
            strSql.Append("RoleName=@roleName,");
            strSql.Append("RoleCode=@roleCode,");
            strSql.Append("IsAdmain=@isAdmain,");
            strSql.Append("Notes=@notes,");
            strSql.Append("SortCode=@sortCode,");
            strSql.Append("UpdateTime=@updateTime,");
            strSql.Append("UpdateUser=@updateUser ");
            strSql.Append(" where ID=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@roleName", model.RoleName),
                    new SqlParameter("@roleCode", model.RoleCode),
                    new SqlParameter("@isAdmain", model.IsAdmain),
                    new SqlParameter("@notes", model.Notes),
                    new SqlParameter("@sortCode", model.SortCode),
                    new SqlParameter("@updateTime", model.UpdateTime),
                    new SqlParameter("@updateUser", model.UpdateUser),
                    new SqlParameter("@id", model.ID)
            };
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            //先删除该角色所有权限
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + tableValueName + " where RoleId=@roleId ");
            SqlParameter[] parameters2 = { new SqlParameter("@roleId", model.ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            //添加权限
            if (model.RoleValues != null)
            {
                StringBuilder roleValueSql;
                foreach (RoleValue modelt in model.RoleValues)
                {
                    roleValueSql = new StringBuilder();
                    roleValueSql.Append("insert into " + tableValueName + "(");
                    roleValueSql.Append("RoleId,MenuId,Module,Authority)");
                    roleValueSql.Append(" values (");
                    roleValueSql.Append("@roleId,@menuId,@module,@authority)");
                    SqlParameter[] parameters3 = {
                            new SqlParameter("@roleId", model.ID),
                            new SqlParameter("@menuId", modelt.MenuId),
                            new SqlParameter("@module", modelt.Module),
                            new SqlParameter("@authority", modelt.Authority)
                    };
                    cmd = new CommandInfo(roleValueSql.ToString(), parameters3);
                    sqllist.Add(cmd);
                }
            }

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据，及子表所有相关数据
        /// </summary>
        public bool DeleteRole(int roleId, string userName)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            //删除管理角色权限
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + tableValueName);
            strSql.Append(" where RoleId=@roleId");
            SqlParameter[] parameters = { new SqlParameter("@roleId", roleId) };
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);
            //删除管理角色
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + tableName);
            strSql2.Append(" where ID=@id");
            SqlParameter[] parameters2 = { new SqlParameter("@id", roleId) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改状态（启用、禁用）
        /// </summary>
        /// <param name="id"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int UpdateStatus(int id, int status, string userName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + tableName + " SET ");
            sb.Append("RoleState=@status,");
            sb.Append("UpdateTime=GetDate(),");
            sb.Append("UpdateUser=@updateUser ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@status",status),
                new SqlParameter("@updateUser",userName),
                new SqlParameter("@id",id),
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }
    }
}